﻿Imports System.Data.OleDb
Imports System.Data.SqlClient

Public Class PhanLoaiThuChiDAO
    Shared conn_str As String = "Data Source=.\sqlexpress;Initial Catalog=QuanLyThuChi;Integrated Security=True"
    Shared Function GetAllPhanLoaiThuChi() As DataTable
        Dim dt As New DataTable
        Dim query As String = "select pl.id, pl.ten_loai, usr.Username, pl.id_user from PhanLoaiThuChi pl, Users usr where pl.id_user = usr.MaUser"
        Try
            Dim adapter As New SqlDataAdapter(query, conn_str)
            adapter.Fill(dt)
        Catch Loi As CustomException.MyException
            Loi.Show(query, conn_str)
        End Try
        Return dt
    End Function
    Shared Function InsertNewItem(ByVal ten As String, ByVal id_user As Integer) As Integer
        Dim dt As Integer
        Dim query As String = "select * from phanloaithuchi where ten_loai = @ten"
        Try
            Dim Ketnoi As SqlConnection = New SqlConnection(conn_str)
            Ketnoi.Open()
            Dim command As SqlCommand = New SqlCommand(query, Ketnoi)
            command.Parameters.Add("@ten", SqlDbType.NVarChar).Value = ten
            dt = command.ExecuteScalar()
            Ketnoi.Close()
        Catch Loi As CustomException.MyException
            Loi.Show(query, conn_str)
        End Try
        If dt = 0 Then
            Dim query1 As String = "insert into phanloaithuchi(ten_loai,id_user) values(@ten_loai,@id_user)"
            Try
                Dim ketnoi As SqlConnection = New SqlConnection(conn_str)
                ketnoi.Open()
                Dim command As SqlCommand = New SqlCommand(query1, ketnoi)
                command.Parameters.Add("@ten_loai", SqlDbType.NVarChar).Value = ten
                command.Parameters.Add("@id_user", SqlDbType.Int).Value = id_user
                command.ExecuteNonQuery()
                Dim command2 As New SqlCommand("Select @@IDENTITY", ketnoi)
                ' Thực hiện lệnh để nhận mã số phát sinh tự động 
                Dim Ma_so As Integer = Integer.Parse(command2.ExecuteScalar())
                ketnoi.Close()
                Return Ma_so
            Catch Loi As CustomException.MyException
                Loi.Show(query1, conn_str)
            End Try
        End If
        Return 0
    End Function
    Shared Function UpdateItem(ByVal pl As PLThuChi) As Integer
        Dim chuoisl As String = "update phanloaithuchi set ten_loai = @tenloai where id = @id"
        Try
            Dim Ketnoi As SqlConnection = New SqlConnection(conn_str)
            Ketnoi.Open()
            Dim Command As SqlCommand = New SqlCommand(chuoisl, Ketnoi)
            Command.Parameters.Add("@tenloai", SqlDbType.NVarChar).Value = pl.ten
            Command.Parameters.Add("@id", SqlDbType.Int).Value = pl.id
            Command.ExecuteNonQuery()
            Ketnoi.Close()
            Return 1
        Catch e As SqlException
            Return -1
        End Try
    End Function
    Shared Function DeleteItem(ByVal id As Integer) As Integer
        Dim chuoisql As String = "delete from phanloaithuchi where id = @id"
        Try
            Dim ketnoi As SqlConnection = New SqlConnection(conn_str)
            ketnoi.Open()
            Dim command As SqlCommand = New SqlCommand(chuoisql, ketnoi)
            command.Parameters.Add("@id", SqlDbType.Int).Value = id
            command.ExecuteNonQuery()
            ketnoi.Close()
            Return 1

        Catch e As SqlException
            Return 0
        End Try
    End Function
End Class
